﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;


namespace WpfSnqkGasAnalysis.IBusiness_impl
{
    /// <summary>
    /// 导出excel
    /// </summary>
    /// 2023-2-21 10:13:22  王浩力 添加
    public class ExportExcelHelpter
    {

        /// <summary>
        /// 导出excel，返回文件流
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="stream">导出流</param>
        /// <remarks>创建时间：2023-2-21 10:43:56。作者：王浩力</remarks>
        public static void ExportExcel(List<List<object>> data, string outPath)
        {
            //注册编码提供程序
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
            Aspose.Cells.Worksheet sheet = wb.Worksheets[0];
            Aspose.Cells.Cells cells = sheet.Cells;
            Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
            //style.Font.Name = "宋体";
            style.Font.Size = 12;

            //文本左对齐
            style.HorizontalAlignment = TextAlignmentType.Left;

            cells.ApplyStyle(style, new StyleFlag() { All = true });
            int cols = data[0].Count;

            //标题样式
            Aspose.Cells.Style styleTitle = wb.Styles[wb.Styles.Add()];
            styleTitle.Font.IsBold = true;
            styleTitle.Font.Size = 16;
            
            //标题背景色
            styleTitle.ForegroundColor = Color.FromArgb(102,255,89);
            styleTitle.Pattern=BackgroundType.Solid;

            Aspose.Cells.Range range = cells.CreateRange(0, 0, 1, cols);
            range.ApplyStyle(styleTitle, new StyleFlag() { All = true });

            //计算值标题单独颜色
            Aspose.Cells.Style styleTitleP = wb.Styles[wb.Styles.Add()];
            styleTitleP.Font.IsBold = true;
            styleTitleP.Font.Size = 16;
            styleTitleP.ForegroundColor = Color.FromArgb(232, 39, 252);
            styleTitleP.Pattern = BackgroundType.Solid;
            Aspose.Cells.Range rangP = cells.CreateRange(0, 45, 1, cols-45);
            rangP.ApplyStyle(styleTitleP, new StyleFlag() { All = true });

            object[,] dataArr2 = new object[data.Count, cols];
            for (int n = 0; n < data.Count; n++)
            {
                var rowLine = data[n];
                for (int j = 0; j < rowLine.Count; j++)
                {
                    dataArr2[n, j] = rowLine[j];
                }
            }

            cells.ImportTwoDimensionArray(dataArr2, 0, 0);

            //for (int i = 0; i < data.Count; i++)
            //{
            //    var item = data[i];
            //    for (int n = 0; n < item.Count; n++)
            //    {
            //        var valueCell = item[n];
            //        cells[i, n].PutValue(valueCell);
            //        if (i == 0)
            //        {
            //            cells[i, n].SetStyle(styleTitle);
            //        }
            //        else {
            //            cells[i, n].SetStyle(style);
            //        }
            //    }
            //}

            //自适应宽
            sheet.AutoFitColumns();
            //自适应行高
            sheet.AutoFitRows();

            //string fileName = Guid.NewGuid().ToString("N") + ".xlsx";
            //string filePath = AppDomain.CurrentDomain.BaseDirectory + fileName;
            //wb.Save(filePath);

            //stream.Seek(0, SeekOrigin.Begin);
            //Stream ms = new MemoryStream();
            //wb.Save(ms, Aspose.Cells.SaveFormat.Xlsx);

            //保存文件到本地
            wb.Save(outPath, SaveFormat.Xlsx);

            //stream.Seek(0, SeekOrigin.Begin);
            //stream = new FileStream(filePath, FileMode.Open);
            //Task.Run(() =>
            //{
            //    //删除生成的文件
            //    System.Threading.Thread.Sleep(1000);
            //    try
            //    {
            //        File.Delete(filePath);
            //    }
            //    catch (Exception) { }
            //});

        }

        /// <summary>
        /// 导出excel到A2，返回文件流
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="stream">导出流</param>
        /// <remarks>创建时间：2023-2-22 14:55:55，作者：王浩力。</remarks>
        public static void ExportExcelA2(List<List<object>> data, string outPath)
        {
            //注册编码提供程序
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
            Aspose.Cells.Worksheet sheet = wb.Worksheets[0];
            Aspose.Cells.Cells cells = sheet.Cells;
            Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
            //style.Font.Name = "宋体";
            style.Font.Size = 12;

            //文本左对齐
            style.HorizontalAlignment = TextAlignmentType.Left;

            cells.ApplyStyle(style, new StyleFlag() { All = true });
            int cols = data[0].Count;

            //标题样式
            Aspose.Cells.Style styleTitle = wb.Styles[wb.Styles.Add()];
            styleTitle.Font.IsBold = true;
            styleTitle.Font.Size = 16;

            //标题背景色
            styleTitle.ForegroundColor = Color.FromArgb(102, 255, 89);
            styleTitle.Pattern = BackgroundType.Solid;

            Aspose.Cells.Range range = cells.CreateRange(0, 0, 1, cols);
            range.ApplyStyle(styleTitle, new StyleFlag() { All = true });

            ////计算值标题单独颜色
            //Aspose.Cells.Style styleTitleP = wb.Styles[wb.Styles.Add()];
            //styleTitleP.Font.IsBold = true;
            //styleTitleP.Font.Size = 16;
            //styleTitleP.ForegroundColor = Color.FromArgb(232, 39, 252);
            //styleTitleP.Pattern = BackgroundType.Solid;
            //Aspose.Cells.Range rangP = cells.CreateRange(0, 45, 1, cols - 45);
            //rangP.ApplyStyle(styleTitleP, new StyleFlag() { All = true });

            object[,] dataArr2 = new object[data.Count, cols];
            for (int n = 0; n < data.Count; n++)
            {
                var rowLine = data[n];
                for (int j = 0; j < rowLine.Count; j++)
                {
                    dataArr2[n, j] = rowLine[j];
                }
            }

            cells.ImportTwoDimensionArray(dataArr2, 0, 0);
             
            //自适应宽
            sheet.AutoFitColumns();
            //自适应行高
            sheet.AutoFitRows();
             
            //保存文件到本地
            wb.Save(outPath, SaveFormat.Xlsx);            
        }


        ///// <summary>
        ///// 导出excel文件，返回生成的web url地址
        ///// </summary>
        ///// <param name="data">导出的数据</param>
        ///// <param name="fileName">生成的文件名称包含后缀</param>
        ///// <returns></returns>
        //public static string ExportExcelFile(List<List<string>> data, string fileName)
        //{
        //    const int font_size = 11;
        //    Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
        //    Aspose.Cells.Worksheet sheet = wb.Worksheets[0];
        //    Aspose.Cells.Cells cells = sheet.Cells;
        //    Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
        //    //style.Font.Name = "宋体";
        //    style.Font.Size = font_size;
        //    cells.ApplyStyle(style, new StyleFlag() { All = true });
        //    int cols = data[0].Count;
        //    //标题样式
        //    Aspose.Cells.Style styleTitle = wb.Styles[wb.Styles.Add()];
        //    styleTitle.Font.IsBold = true;
        //    styleTitle.Font.Size = font_size;
        //    Range range = cells.CreateRange(0, 0, 1, cols);
        //    range.ApplyStyle(styleTitle, new StyleFlag() { All = true });

        //    object[,] dataArr2 = new object[data.Count, cols];
        //    for (int n = 0; n < data.Count; n++)
        //    {
        //        var rowLine = data[n];
        //        for (int j = 0; j < rowLine.Count; j++)
        //        {
        //            dataArr2[n, j] = rowLine[j];
        //        }
        //    }
        //    cells.ImportTwoDimensionArray(dataArr2, 0, 0);

        //    //自适应宽
        //    //sheet.AutoFitColumns();
        //    //自适应行高
        //    sheet.AutoFitRows();

        //    //string fileName = Guid.NewGuid().ToString("N") + ".xls";
        //    string dir = AppDomain.CurrentDomain.BaseDirectory + Models.Veiw.CommonData.ExportFilefolder;
        //    if (!System.IO.Directory.Exists(dir))
        //    {
        //        System.IO.Directory.CreateDirectory(dir);
        //    }
        //    string filePath = dir + fileName;
        //    //保存文件到本地
        //    wb.Save(filePath);
        //    string url = Models.Veiw.CommonData.ExportFilefolder.Replace(@"\", @"/") + fileName;

        //    //记录生成的文件到缓存，用于删除
        //    const string cacheKey = "XLS_EXPORT_TO_DEL";
        //    List<string> filesExportList = MemoryCacheProvider.GetCacheItem<List<string>>(cacheKey);

        //    //删除已经过期的文件
        //    Action<List<string>> ac = (list) =>
        //    {
        //        if (list == null || list.Count == 0)
        //        {
        //            return;
        //        }
        //        foreach (var delFile in list)
        //        {
        //            if (File.Exists(delFile))
        //            {
        //                File.Delete(delFile);
        //            }
        //        }
        //    };

        //    if (filesExportList != null)
        //    {
        //        //var item = (filePath, DateTime.UtcNow);
        //        filesExportList.Add(filePath);
        //        MemoryCacheProvider.Set(cacheKey, filesExportList, DateTime.UtcNow.AddMinutes(240));
        //    }
        //    else
        //    {
        //        filesExportList = new List<string>();
        //        filesExportList.Add(filePath);
        //        MemoryCacheProvider.Set(cacheKey, filesExportList, DateTime.UtcNow.AddMinutes(240));
        //    }
        //    //删除文件
        //    Task.Run(() =>
        //    {
        //        System.Threading.Thread.Sleep(30000);
        //        ac(filesExportList);
        //    });

        //    return url;
        //}



    }
}
